package day20171010;

import com.mysql.jdbc.Connection;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;

import java.io.*;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Created by 14394 on 2017/10/10.
 */
public class GrabProject2 {
    private  Connection conn;
    private static PreparedStatement ps;

    public static void main(String[] args) {

        String tabName = "Spider1";
        String[] tab_fields = {"网页源码", "url地址", "抓取时间"};
        new GrabProject2().createTable(tabName, tab_fields);

        File file = new File("d:\\dev\\poi_ids.txt");
        int startid = 0;
        int endid = 0;
        BufferedReader reader = null;
        BufferedReader readerStartId = null;
        Writer writer = null;
        try {
            readerStartId = new BufferedReader(new FileReader("D:\\dev\\log.txt"));
            String theContents = readerStartId.readLine();
            if (theContents  == null||theContents.length()==0) {
                startid = 0;
            } else {
                for (int i=0;i<theContents.length();i++){
                    startid+=((int)theContents.charAt(i)-48)*Math.pow(10,theContents.length()-i-1);
                }
                System.out.println(startid);
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            reader = new BufferedReader(new FileReader(file));
            int i = 0;
            int j = 0;
            String id = null;
            try {
                BufferedReader reader1 = new BufferedReader(new FileReader(file));
                while (reader1.readLine() != null) {
                    i++;
                   // if (i == 3) {
                    //    break;
                  //  }
                }
                ;
            } catch (IOException e0) {
                e0.printStackTrace();
            }
            System.out.println(i);
            String[] ids = new String[i];
            while ((id = reader.readLine()) != null) {
                int h = 0;
                ids[h = ++j - 1] = id;
               // if (h == 2) break;
                //System.out.println(id);
            }
            // System.out.println(ids[0]);
            // System.out.println(i);
            // WritableWorkbook wwb;
            // OutputStream outputStream = null;
            //Label label;
            //try {
            //  outputStream = new FileOutputStream("D:\\dev\\Grab_mafengwo.xls");
            //wwb = Workbook.createWorkbook(outputStream);
            // WritableSheet ws = wwb.createSheet("马蜂窝网页", 0);
            //  label = new Label(0, 0, "序号");
            // ws.addCell(label);
            // label = new Label(1, 0, "网页源码");
            //ws.addCell(label);
            // label = new Label(2, 0, "网址");
            // ws.addCell(label);
            // label = new Label(3, 0, "抓取时间");
            // ws.addCell(label);
            for (int m = 0; m < i; m++) {
                if (m < startid) continue;
                endid = m;
                Document d = Jsoup.connect("http://www.mafengwo.cn/poi/" + ids[m] + ".html").get();
                System.out.println(m);
                String[] data1 = {String.valueOf(d), "http://www.mafengwo.cn/poi/" + ids[m] + ".html", "" + System.currentTimeMillis()};
                if(endid>0) tabName = "Spider" + (m /  10000+1);
                    if (m % 10000 == 0) {
                        String newTable = "Spider" + (m / 10000+1);
                        String[] new_tab_fields = {"网页源码", "url地址", "抓取时间"};
                        new GrabProject2().createTable(newTable, new_tab_fields);
                        tabName = newTable;
                    }
                    new GrabProject2().insert(tabName, tab_fields, data1);

                if (m % 10 == 0 && m != 0) {//限制熟虑不要太高
                    try {
                        Thread.sleep(20000);
                    } catch (Exception ee) {
                    }
                    if(m%100==0&&m!=0) {//限制一次抓取量不要太大
                        try {
                            Thread.sleep(60000);
                        } catch (Exception ee) {
                        }
                        if(m%700==0&&m!=0) {//限制一次抓取量不要太大
                            try {
                                Thread.sleep(90000);
                            } catch (Exception ee) {
                            }
                         }
                    }


                    }
                }

                //label=new Label(1,m+1,String.valueOf(d));
                //ws.addCell(label);


            // wwb.write();
            // wwb.close();
        } catch (Exception e1) {
            try {
                writer = new BufferedWriter(new FileWriter("D:\\dev\\log.txt"));
                writer.write(""+(endid+1));
                writer.close();
            } catch (IOException e) {
            }
            System.out.println("未知异常,爬到了第" + (int)(endid ) + "条网页");
            e1.printStackTrace();
        }
    }

    public  void insert(String tabName, String[] fields, String[] data) {
        conn = InitDB.getInitDb().getConnection();
        try {
            String sql = "insert into " + tabName + "(";
            int length = fields.length;
            for (int i = 0; i < length; i++) {
                sql += fields[i];
                if (i < length - 1) {
                    sql += ",";
                }
            }
            sql += ") values(";
            for (int i = 0; i < length; i++) {
                sql += "?";
                if (i < length - 1) {
                    sql += ",";
                }
            }
            sql += ");";
            System.out.println("添加数据的sql:" + sql);
            //预处理SQL 防止注入
            excutePs(sql, length, data);
            ps.executeUpdate();
            ps.close();
           // conn.close();
        } catch (SQLException e) {
            System.out.println("添加数据失败" + e.getMessage());
        }
    }

    //用于注入参数
    private  void excutePs(String sql, int length, String[] data) throws SQLException {
        ps = conn.prepareStatement(sql);
        for (int i = 0; i < length; i++) {
            ps.setString(i + 1, data[i]);
        }
    }


    public  void createTable(String tabName, String[] tab_fileds) {
        //首先要获得连接
        conn = new InitDB().getConnection();
        try {
            String sql = "create table " + tabName + "(id int auto_increment primary key not null,";
            if (tab_fileds != null && tab_fileds.length > 0) {
                sql += " ";
                int length = tab_fileds.length;
                for (int i = 0; i < length; i++) {
                    if (i == 0) sql += tab_fileds[i].trim() + " longblob";
                    if (i == 1) sql += tab_fileds[i].trim() + " varchar(50)";
                    if (i == 2) sql += tab_fileds[i].trim() + " varchar(50)";
                    if (i < length - 1) {
                        sql += ",";
                    }
                }
            }
            sql += ")DEFAULT CHARSET=utf8;";
            System.out.println("创建语句是" + sql);
            ps = conn.prepareStatement(sql);
            ps.executeUpdate(sql);
            ps.close();
            //conn.close();
        } catch (SQLException e) {
            System.out.println("建表失败" + e.getMessage());
        }
    }
}
